﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Net.SourceForge.Koogra;
using Excel97 = Net.SourceForge.Koogra.Excel;
using Excel2007 = Net.SourceForge.Koogra.Excel2007;

namespace Wicresoft.SMS.Common
{
    /// <summary>
    /// 代码修改自来源 http://kb.cnblogs.com/a/1648860/
    /// </summary>
    public class ExcelUtils
    {

        private IWorkbook book;
        private bool IsExcel97 = true;

        public ExcelUtils(string path)
        {
            // this.book = new Workbook(path);
        }

        public ExcelUtils(System.IO.Stream stream)
        {
            //this.book = new Workbook(stream);
        }

        public ExcelUtils(System.IO.Stream stream, bool isexcel97)
        {
            this.IsExcel97 = isexcel97;
            if (isexcel97)
            {
                try
                {
                    this.book = new Excel97.Workbook(stream);
                }
                catch (Exception ex)
                {
                    this.book = new Excel2007.Workbook(stream);
                }
            }
            else
            {
                try
                {
                    this.book = new Excel2007.Workbook(stream);
                }
                catch (Exception)
                {
                    this.book = new Excel97.Workbook(stream);
                }
            }
        }

        protected DataTable SaveAsDataTable(IWorksheet sheet)
        {
            DataTable dt = new DataTable();

            if (sheet.LastCol > sheet.FirstCol && sheet.LastRow > sheet.FirstRow)
            {
                IRow firstrow = sheet.Rows.GetRow(sheet.FirstRow);
                for (uint i = sheet.FirstCol; i <= sheet.LastCol; i++)
                {
                    dt.Columns.Add(firstrow.GetCell(i).Value.ToString().Trim().ToLower());
                }

                for (uint i = 1; i <= sheet.LastRow; i++)
                {
                    IRow row = sheet.Rows.GetRow(i);
                    if (row != null)
                    {
                        DataRow dr = dt.NewRow();
                        for (uint j = sheet.FirstCol; j <= sheet.LastCol; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                dr[Convert.ToInt32(j)] = cell.Value != null ? cell.Value.ToString().Trim() : string.Empty;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }


            return dt;
        }

        public DataTable ToDataTable(string sheetName)
        {
            IWorksheets sheets = this.book.Worksheets;
            IWorksheet sheet = sheets.GetWorksheetByName(sheetName);

            if (sheet == null)
            {
                throw new ApplicationException(string.Format("名称[{0}]所指定的电子表格不存在！", sheetName));
            }

            return this.SaveAsDataTable(sheet);
        }

        public DataTable ToDataTable(int sheetIndex)
        {
            IWorksheets sheets = this.book.Worksheets;

            if (sheets.Count == 0)
            {
                throw new ApplicationException(string.Format("Index[{0}]所指定的电子表格不存在！", sheetIndex));
            }

            IWorksheet sheet = sheets.GetWorksheetByIndex(sheetIndex);

            if (sheet == null)
            {
                throw new ApplicationException(string.Format("Index[{0}]所指定的电子表格不存在！", sheetIndex));
            }

            return this.SaveAsDataTable(sheet);
        }

        #region 静态方法

        /// <summary>
        /// 单元格格式为日期时间，使用此方法转换为DateTime类型，若解析失败则返回‘0001-01-01’
        /// </summary>
        public static DateTime ParseDateTime(string cellValue)
        {
            DateTime date = default(DateTime);

            double value = default(double);

            if (double.TryParse(cellValue, out value))
            {
                date = DateTime.FromOADate(value);
            }
            else
            {
                DateTime.TryParse(cellValue, out date);
            }

            return date;
        }

        /// <summary>
        /// 转换为DataTable(内存流+表名)
        /// </summary>
        public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName, bool isexcel97)
        {
            ExcelUtils utils = new ExcelUtils(stream, isexcel97);
            return utils.ToDataTable(sheetName);
        }

        /// <summary>
        /// 转换为DataTable(内存流+INDEX)
        /// </summary>
        public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex, bool isexcel97)
        {
            ExcelUtils utils = new ExcelUtils(stream, isexcel97);
            return utils.ToDataTable(sheetIndex);
        }
        #endregion

    }
}
